# -*- coding: utf-8 -*-
# 生产者消费者爬虫
# Description: python 多线程-普通多线程-生产者消费者模型
import datetime
import json
import random
import time
import pymysql
from queue import Queue
from threading import Thread

import requests

# 商品

goods_list = {
    "粳米": "210010",
    "籼米": "210020",
    "面粉": "210030",
    "豆油": "220010",
    "花生油": "220020",
    "菜籽油": "220030",
    "调和油": "220050",
    "白条猪": "130010",
    "后臀尖": "130014",
    "牛腿肉": "130025",
    "整牛": "130020",
    "羊腿肉": "130035",
    "整羊": "130030",
    "白条鸡": "280020",
    "白条鸭": "280050",
    "鲜鸡蛋": "150010",
    "油菜": "170020",
    "芹菜": "170040",
    "生菜": "170050",
    "韭菜": "170480",
    "洋葱": "170090",
    "大白菜": "170060",
    "圆白菜": "170010",
    "白萝卜": "170070",
    "大葱": "170250",
    "胡萝卜": "170260",
    "土豆": "170080",
    "莲藕": "170270",
    "莴笋": "170280",
    "绿豆芽": "170290",
    "蒜头": "170100",
    "西红柿": "170120",
    "生姜": "170110",
    "茄子": "170140",
    "尖椒": "170150",
    "青椒": "170160",
    "黄瓜": "170130",
    "冬瓜": "170180",
    "苦瓜": "170200",
    "西葫芦": "170330",
    "西兰花": "170340",
    "菠菜": "170440",
    "菜花": "170450",
    "山药": "170500",
    "南瓜": "170470",
    "豆角": "170170",
}


# 类别
type_list = {
    "粳米": "粮油类",
    "籼米": "粮油类",
    "面粉": "粮油类",
    "豆油": "粮油类",
    "花生油": "粮油类",
    "菜籽油": "粮油类",
    "调和油": "粮油类",
    "白条猪": "肉类",
    "后臀尖": "肉类",
    "牛腿肉": "肉类",
    "整牛": "肉类",
    "羊腿肉": "肉类",
    "整羊": "肉类",
    "白条鸡": "禽蛋类",
    "白条鸭": "禽蛋类",
    "鲜鸡蛋": "禽蛋类",
    "油菜": "蔬菜类",
    "芹菜": "蔬菜类",
    "生菜": "蔬菜类",
    "韭菜": "蔬菜类",
    "洋葱": "蔬菜类",
    "大白菜": "蔬菜类",
    "圆白菜": "蔬菜类",
    "白萝卜": "蔬菜类",
    "大葱": "蔬菜类",
    "胡萝卜": "蔬菜类",
    "土豆": "蔬菜类",
    "莲藕": "蔬菜类",
    "莴笋": "蔬菜类",
    "绿豆芽": "蔬菜类",
    "蒜头": "蔬菜类",
    "西红柿": "蔬菜类",
    "生姜": "蔬菜类",
    "茄子": "蔬菜类",
    "尖椒": "蔬菜类",
    "青椒": "蔬菜类",
    "黄瓜": "蔬菜类",
    "冬瓜": "蔬菜类",
    "苦瓜": "蔬菜类",
    "西葫芦": "蔬菜类",
    "西兰花": "蔬菜类",
    "菠菜": "蔬菜类",
    "菜花": "蔬菜类",
    "山药": "蔬菜类",
    "南瓜": "蔬菜类",
    "豆角": "蔬菜类",
}

# 用户代理池
uapools = [
    "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/39.0.2171.95 Safari/537.36 OPR/26.0.1656.60",
    "Opera/8.0 (Windows NT 5.1; U; en)",
    "Mozilla/5.0 (Windows NT 5.1; U; en; rv:1.8.1) Gecko/20061208 Firefox/2.0.0 Opera 9.50",
    "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; en) Opera 9.50",
    "Opera/9.80 (Macintosh; Intel Mac OS X 10.6.8; U; en) Presto/2.8.131 Version/11.11",
    "Opera/9.80 (Windows NT 6.1; U; en) Presto/2.8.131 Version/11.11",
    "Opera/9.80 (Android 2.3.4; Linux; Opera Mobi/build-1107180945; U; en-GB) Presto/2.8.149 Version/11.10",
    "Mozilla/5.0 (Windows NT 6.1; WOW64; rv:34.0) Gecko/20100101 Firefox/34.0",
    "Mozilla/5.0 (X11; U; Linux x86_64; zh-CN; rv:1.9.2.10) Gecko/20100922 Ubuntu/10.10 (maverick) Firefox/3.6.10",
    "Mozilla/5.0 (Macintosh; Intel Mac OS X 10.6; rv,2.0.1) Gecko/20100101 Firefox/4.0.1",
    "Mozilla/5.0 (Windows NT 6.1; rv,2.0.1) Gecko/20100101 Firefox/4.0.1",
    "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/534.57.2 (KHTML, like Gecko) Version/5.1.7 Safari/534.57.2",
    "MAC：Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/81.0.4044.122 Safari/537.36",
    "Windows：Mozilla/5.0 (Windows; U; Windows NT 6.1; en-us) AppleWebKit/534.50 (KHTML, like Gecko) Version/5.1 Safari/534.50",
    "Mozilla/5.0 (iPhone; U; CPU iPhone OS 4_3_3 like Mac OS X; en-us) AppleWebKit/533.17.9 (KHTML, like Gecko) Version/5.0.2 Mobile/8J2 Safari/6533.18.5",
    "Mozilla/5.0 (iPhone; U; CPU iPhone OS 4_3_3 like Mac OS X; en-us) AppleWebKit/533.17.9 (KHTML, like Gecko) Version/5.0.2 Mobile/8J2 Safari/6533.18.5",
    "Mozilla/5.0 (iPad; U; CPU OS 4_3_3 like Mac OS X; en-us) AppleWebKit/533.17.9 (KHTML, like Gecko) Version/5.0.2 Mobile/8J2 Safari/6533.18.5",
    "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/39.0.2171.71 Safari/537.36",
    "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.11 (KHTML, like Gecko) Chrome/23.0.1271.64 Safari/537.11",
    "Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US) AppleWebKit/534.16 (KHTML, like Gecko) Chrome/10.0.648.133 Safari/534.16",
    "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_7_0) AppleWebKit/535.11 (KHTML, like Gecko) Chrome/17.0.963.56 Safari/535.11",
    "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/30.0.1599.101 Safari/537.36",
    "Mozilla/5.0 (Windows NT 6.1; WOW64; Trident/7.0; rv:11.0) like Gecko",
    "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; 360SE)",
    "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/536.11 (KHTML, like Gecko) Chrome/20.0.1132.11 TaoBrowser/2.0 Safari/536.11",
    "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.1 (KHTML, like Gecko) Chrome/21.0.1180.71 Safari/537.1 LBBROWSER",
    "Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; WOW64; Trident/5.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; .NET4.0E; LBBROWSER)",
    "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; QQDownload 732; .NET4.0C; .NET4.0E; LBBROWSER)"
    "Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; WOW64; Trident/5.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; .NET4.0E; QQBrowser/7.0.3698.400)",
    "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; QQDownload 732; .NET4.0C; .NET4.0E)",
    "Mozilla/5.0 (Windows NT 5.1) AppleWebKit/535.11 (KHTML, like Gecko) Chrome/17.0.963.84 Safari/535.11 SE 2.X MetaSr 1.0",
    "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; Trident/4.0; SV1; QQDownload 732; .NET4.0C; .NET4.0E; SE 2.X MetaSr 1.0)",
    "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; Trident/4.0; SE 2.X MetaSr 1.0; SE 2.X MetaSr 1.0; .NET CLR 2.0.50727; SE 2.X MetaSr 1.0)",
    "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Maxthon/4.4.3.4000 Chrome/30.0.1599.101 Safari/537.36",
    "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_7_0) AppleWebKit/535.11 (KHTML, like Gecko) Chrome/17.0.963.56 Safari/535.11",
    "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/38.0.2125.122 UBrowser/4.0.3214.0 Safari/537.36",
    "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/55.0.2883.87 UBrowser/6.2.4094.1 Safari/537.36",
    "Mozilla/5.0 (iPhone; U; CPU iPhone OS 4_3_3 like Mac OS X; en-us) AppleWebKit/533.17.9 (KHTML, like Gecko) Version/5.0.2 Mobile/8J2 Safari/6533.18.5",
    "Mozilla/5.0 (iPod; U; CPU iPhone OS 4_3_3 like Mac OS X; en-us) AppleWebKit/533.17.9 (KHTML, like Gecko) Version/5.0.2 Mobile/8J2 Safari/6533.18.5",
    "Mozilla/5.0 (iPad; U; CPU OS 4_2_1 like Mac OS X; zh-cn) AppleWebKit/533.17.9 (KHTML, like Gecko) Version/5.0.2 Mobile/8C148 Safari/6533.18.5",
    "Mozilla/5.0 (iPad; U; CPU OS 4_3_3 like Mac OS X; en-us) AppleWebKit/533.17.9 (KHTML, like Gecko) Version/5.0.2 Mobile/8J2 Safari/6533.18.5",
    "Mozilla/5.0 (Linux; U; Android 2.2.1; zh-cn; HTC_Wildfire_A3333 Build/FRG83D) AppleWebKit/533.1 (KHTML, like Gecko) Version/4.0 Mobile Safari/533.1",
    "Mozilla/5.0 (Linux; U; Android 2.3.7; en-us; Nexus One Build/FRF91) AppleWebKit/533.1 (KHTML, like Gecko) Version/4.0 Mobile Safari/533.1",
    "MQQBrowser/26 Mozilla/5.0 (Linux; U; Android 2.3.7; zh-cn; MB200 Build/GRJ22; CyanogenMod-7) AppleWebKit/533.1 (KHTML, like Gecko) Version/4.0 Mobile Safari/533.1",
    "Opera/9.80 (Android 2.3.4; Linux; Opera Mobi/build-1107180945; U; en-GB) Presto/2.8.149 Version/11.10",
    "Mozilla/5.0 (Linux; U; Android 3.0; en-us; Xoom Build/HRI39) AppleWebKit/534.13 (KHTML, like Gecko) Version/4.0 Safari/534.13",
    "Mozilla/5.0 (BlackBerry; U; BlackBerry 9800; en) AppleWebKit/534.1+ (KHTML, like Gecko) Version/6.0.0.337 Mobile Safari/534.1+",
    "Mozilla/5.0 (hp-tablet; Linux; hpwOS/3.0.0; U; en-US) AppleWebKit/534.6 (KHTML, like Gecko) wOSBrowser/233.70 Safari/534.6 TouchPad/1.0",
    "Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; Trident/5.0;",
    "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.0)",
    "Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.0; Trident/4.0)",
    "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)",
    "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1)",
    "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; The World)",
    "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; TencentTraveler 4.0)",
    "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; Avant Browser)",
    "Mozilla/5.0 (Linux; U; Android 2.3.7; en-us; Nexus One Build/FRF91) AppleWebKit/533.1 (KHTML, like Gecko) Version/4.0 Mobile Safari/533.1",
    "Mozilla/5.0 (SymbianOS/9.4; Series60/5.0 NokiaN97-1/20.0.019; Profile/MIDP-2.1 Configuration/CLDC-1.1) AppleWebKit/525 (KHTML, like Gecko) BrowserNG/7.1.18124",
    "Mozilla/5.0 (compatible; MSIE 9.0; Windows Phone OS 7.5; Trident/5.0; IEMobile/9.0; HTC; Titan)",
    "UCWEB7.0.2.37/28/999",
    "NOKIA5700/ UCWEB7.0.2.37/28/999",
    "Openwave/ UCWEB7.0.2.37/28/999",
    "Openwave/ UCWEB7.0.2.37/28/999",
]


def jsonFile(jsonData, fileName):
    with open(fileName, "w", encoding="utf8") as f:
        json.dump(jsonData, f, ensure_ascii=False)


def producer(begin, end, in_q):  # 生产者
    ready_list = []
    while in_q.full() is False:
        # 爬取蔬菜类商品数据
        for goods in goods_list:
            searchDate = begin
            delta = datetime.timedelta(days=1)
            while searchDate <= end:
                searchDate.strftime("%Y-%m-%d")
                searchDate += delta
                url = goods_list.get(goods) + " " + str(searchDate) + " " + goods + " " + type_list.get(goods)
                if url not in ready_list:
                    ready_list.append(url)
                    in_q.put(url)
                else:
                    continue


def consumer(in_q, out_q):  # 消费者
    headers = {
        'Accept': '',
        'Accept-Language': 'zh-CN,zh;q=0.9,en;q=0.8',
        'Connection': 'keep-alive',
        'Cookie': '',
        'DNT': '1',
        'Host': 'www..com',
        'Referer': 'http://www.g.com',
        'Upgrade-Insecure-Requests': '1',
        'User-Agent': random.choice(uapools)
    }
    while True:
        try:
            url = 'https://cif.mofcom.gov.cn/cif/getEnterpriseListForDate.fhtml'
            param_list = in_q.get().split(" ")
            param = {
                "cateId": str(param_list[0]),
                "searchDate": str(param_list[1])
            }
            # print(param)
            data = requests.post(url=url, headers=headers, data=param, timeout=5)
            r = data.content
            content = data.text
            # print(content)
            # content = str(r, encoding='utf-8', errors='ignore')
            price_list_json = json.loads(content)
            for j in range(len(price_list_json['datas'])):
                price_obj = {
                    "date": price_list_json['date'],
                    "price": price_list_json['datas'][j]['PRICE2'],
                    "goods": price_list_json['datas'][j]['COMMDITYNAME'],
                    "county": price_list_json['datas'][j]['COUNTY_NAME'],
                    "provider": price_list_json['datas'][j]['NAME'],
                    "type": param_list[3],
                    "goodsId": param_list[0]
                }
                out_q.put(price_obj)
                print(price_obj)
            in_q.task_done()  # 通知生产者，队列已消化完
        except Exception as e:
            in_q.task_done()  # 通知生产者，队列已消化完
            # 发生错误有五次重试机会
            num = 5
            print("ERROR!!!" + str(param_list))
            print(e)
            while num > 0:
                if error(param_list, out_q):
                    break
                num -= 1


def error(param_list, out_q):
    headers = {
        'Accept': '',
        'Accept-Language': 'zh-CN,zh;q=0.9,en;q=0.8',
        'Connection': 'keep-alive',
        'Cookie': '',
        'DNT': '1',
        'Host': 'www..com',
        'Referer': 'http://www.g.com',
        'Upgrade-Insecure-Requests': '1',
        'User-Agent': random.choice(uapools)
    }
    try:
        url = 'https://cif.mofcom.gov.cn/cif/getEnterpriseListForDate.fhtml'
        param = {
            "cateId": str(param_list[0]),
            "searchDate": str(param_list[1])
        }
        # print(param)
        data = requests.post(url=url, headers=headers, data=param, timeout=5)
        r = data.content
        content = data.text
        # print(content)
        # content = str(r, encoding='utf-8', errors='ignore')
        price_list_json = json.loads(content)
        for j in range(len(price_list_json['datas'])):
            price_obj = {
                "date": price_list_json['date'],
                "price": price_list_json['datas'][j]['PRICE2'],
                "goods": price_list_json['datas'][j]['COMMDITYNAME'],
                "county": price_list_json['datas'][j]['COUNTY_NAME'],
                "provider": price_list_json['datas'][j]['NAME'],
                "type": param_list[3],
                "goodsId": param_list[0]
            }
            out_q.put(price_obj)
            print(price_obj)
            return True
    except Exception as e:
        print("ERROR!!!" + str(param_list))
        print(e)
        return False


if __name__ == '__main__':
    start = time.time()
    queue = Queue(maxsize=100)  # 设置队列最大空间为200
    result_queue = Queue()
    # print('queue 开始大小 %d' % queue.qsize())
    # 定义停止爬取日期
    end = datetime.date.today()
    interval = datetime.timedelta(days=1)
    # 定义开始爬取日期
    begin = end - interval

    producer_thread = Thread(target=producer, args=(begin, end, queue,))
    producer_thread.daemon = True
    producer_thread.start()

    for index in range(10):
        consumer_thread = Thread(target=consumer, args=(queue, result_queue,))
        consumer_thread.daemon = True
        consumer_thread.start()

    queue.join()
    endtime = time.time()

    print('总耗时：%s' % (endtime - start))
    print("共获取到 [ " + str(result_queue.qsize()) + " ] 条数据")
    print("保存数据中...")
    # 数据存储
    param = {
        'host': 'localhost',
        'port': 3306,
        'db': 'ehe',
        'user': 'root',
        'password': 'root',
        'charset': 'utf8',
    }
    conn = pymysql.connect(**param)  # 连接对象
    cur = conn.cursor()  # 游标对象，采用默认的数据格式

    # 数据列表
    result_list = []
    for j in range(result_queue.qsize()):
        result_list.append(result_queue.get())

    for obj in result_list:
        # print(obj)
        # %s:占位符
        # params：增加内容的列表或元组,多条语句可以使用嵌套
        sql = "insert into t_price_data(goods,goods_id,date,price,county,provider,type) values(%s,%s,%s,%s,%s,%s,%s)"
        params = (
        obj['goods'], obj['goodsId'], obj['date'], float(obj['price']), obj['county'], obj['provider'], obj['type'])
        cur.execute(sql, params)  # sql语句参数化，防止攻击！

    for good in goods_list:

        searchDate = begin
        delta = datetime.timedelta(days=1)
        while searchDate < end:
            num = 0
            price = 0
            print("good: " + good + " searchDate: " + str(searchDate))

            for obj in result_list:
                # print("obj.good: " + obj['goods'] + " obj,date: " + obj['date'])
                if obj['goods'] == good and obj['date'] == str(searchDate):
                    # print("满足条件")
                    # print(obj)
                    price += float(obj['price'])
                    num += 1
            # print("price: " + str(price) + " num: " + str(num))
            if num != 0:
                print("date: " + str(searchDate) + " goods: " + good + " price: " + (format(price / num, '.2f')))
                sql = "insert into t_avg_price_data(date,goods,price) values(%s,%s,%s)"
                params = (str(searchDate), good, format(price / num, '.2f'))
                cur.execute(sql, params)  # sql语句参数化，防止攻击！
            searchDate.strftime("%Y-%m-%d")
            searchDate += delta

    # pymysql连接数据库默认开启事物，提交之前的操作，使生效！
    conn.commit()
    print("数据保存完毕")
    # 要及时关闭连接！
    cur.close()  # 关闭游标
    conn.close()  # 关闭连接

    # jsonFile(result_list, "Data\\price_list.json")
